package com.dy.yunying.biz.dao.datacenter.impl;

import com.dy.yunying.api.dto.audience.AudiencePackDto;
import com.dy.yunying.api.vo.audience.AudiencePackVo;
import com.dy.yunying.biz.config.YunYingProperties;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeFormatterBuilder;
import java.util.List;

/**
 * @Description
 * @Author chengang
 * @Date 2022/7/18
 */
@Slf4j
@Component
public class AudiencePackDao {

	private static final DateTimeFormatter DATE_FORMATTER = new DateTimeFormatterBuilder().appendPattern("yyyyMMdd").toFormatter();

	@Resource(name = "clickDcSessionTemplate")
	private JdbcTemplate clickhouseTemplate;

	@Resource
	private YunYingProperties yunYingProperties;

	public Long selectCount(AudiencePackDto dto,boolean isPage) {
		final String countSqlStr = this.getCountSqlBuilder(dto, isPage).toString();
		final Long count = clickhouseTemplate.queryForObject(countSqlStr, (row, idx) -> requireLong(row.getObject(1)));
		log.info("人群打包总条数SQL({}): \n[{}]",count, countSqlStr);
		return null == count ? 0L : count;
	}

	public List<AudiencePackVo> selectByPage(AudiencePackDto dto,boolean isPage) {
		final String listSqlStr = this.getPageSqlBuilder(dto, isPage).toString();
		log.info("人群打包分页SQL: \n[{}]", listSqlStr);
		return clickhouseTemplate.query(listSqlStr, audienceMapping(dto));
	}

	private StringBuilder getCountSqlBuilder(AudiencePackDto dto, boolean isPage) {
		String pageSql = this.getPageSqlBuilder(dto,false).toString();
		StringBuilder sql = new StringBuilder();
		sql.append(" select count(1) from ( \n").append(pageSql);
		sql.append(" ) ");
		return sql;
	}

	private StringBuilder getPageSqlBuilder(AudiencePackDto dto, boolean isPage) {
		StringBuilder sql = new StringBuilder();
		sql.append(" select \n");
		sql.append(" 	d_kid,\n");
		sql.append(" 	game_main as pgid,\n");
		sql.append(" 	imei, \n ");
		sql.append(" 	idfa, \n");
		sql.append(" 	oaid, \n ");
		sql.append(" 	mac, \n");
		sql.append(" 	moneyAmount as payfee, \n ");
		sql.append(" 	loginDays as activenum, \n ");
		sql.append(" 	MAX(days) as activemaxnum, \n");
		sql.append(" 	argMax(startTime,days) as startTime, \n");
		sql.append(" 	argMax(endTime,	days) as endTime \n");
		sql.append(" from ( \n");
		sql.append(" 	select \n");
		sql.append(" 		d_kid,\n");
		sql.append(" 		game_main,\n ");
		sql.append(" 		imei, \n ");
		sql.append(" 		idfa,\n ");
		sql.append(" 		oaid, \n");
		sql.append(" 		mac, \n");
		sql.append(" 		moneyAmount, \n");
		sql.append(" 		loginDays,\n");
		sql.append(" 		count(1) as days, \n");
		sql.append(" 		addDays(diff,min(idx)) as startTime,\n");
		sql.append(" 		addDays(diff,max(idx)) as endTime \n ");
		sql.append(" 	from  \n");
		sql.append(" 		( \n ");
		sql.append(" 		select \n ");
		sql.append(" 			activity.d_kid, \n");
		sql.append(" 			reg.game_main,\n ");
		sql.append(" 			reg.imei, \n");
		sql.append(" 			reg.idfa,\n");
		sql.append(" 			reg.oaid, \n ");
		sql.append(" 			reg.mac, \n ");
		sql.append(" 			COALESCE(reg.fee_total + reg.givemoney_total,0) moneyAmount,\n ");
		sql.append(" 			activity.loginDays, \n");
		sql.append(" 			subtractDays(ad,idx) as diff, \n");
		sql.append(" 			ad, \n ");
		sql.append(" 			idx \n");
		sql.append(" 		from \n ");
		sql.append(" 			(\n");
		sql.append(" 			select \n ");
		sql.append(" 				argMax(act.d_kid,act.active_day) as d_kid,-- 最大活跃时间对应的设备kid \n");
		sql.append(" 				arraySort(groupUniqArray(toDate(parseDateTimeBestEffort(toString(act.active_day))))) as adarr,\n");
		sql.append(" 				arrayEnumerate(arraySort(groupUniqArray(toString(act.active_day)))) as arr_idx,\n");
		sql.append(" 				sum(IF(act.data_tag = 1, 1, 0)) as loginDays\n ");
		sql.append(" 			from   ").append(yunYingProperties.getDeviceregisterlogintable()).append("  act");
		sql.append(" 			where\n ");
		sql.append(" 				act.data_tag = 1-- 只是登录活跃 \n");
		if (StringUtils.isNotBlank(dto.getStartDate())) {
			sql.append(" 	AND act.active_day >= ").append(dto.getStartDate()).append(" \n");
		}
		if (StringUtils.isNotBlank(dto.getEndDate())) {
			sql.append(" 	AND act.active_day <= ").append(dto.getEndDate()).append(" \n");
		}
		if (StringUtils.isNotBlank(dto.getPgidArr())) {
			sql.append(" 				AND act.game_main IN ( ").append(dto.getPgidArr()).append(" )\n ");
		}

		sql.append(" 			group by act.d_kid\n ");
		sql.append(" 		  ) activity array  \n");
		sql.append(" 		join adarr as ad,arr_idx as idx \n");
		sql.append(" 		left join " + yunYingProperties.getNinetydeviceregtable() + " reg    on	reg.kid = activity.d_kid \n");
		sql.append(" 		where 1 = 1\n ");

		if (StringUtils.isNotBlank(dto.getPgidArr())) {
			sql.append(" 				AND reg.game_main IN ( ").append(dto.getPgidArr()).append(" )\n ");
		}

		if (StringUtils.isNotBlank(dto.getStartDate())) {
			sql.append(" 			AND reg.reg_day >= ").append(dto.getStartDate());
		}
		if (StringUtils.isNotBlank(dto.getEndDate())) {
			sql.append(" 			AND reg.reg_day <= ").append(dto.getEndDate());
		}
		if (StringUtils.isNotBlank(dto.getStartPay())) {
			sql.append(" 			AND reg.fee_total + reg.givemoney_total >= ").append(dto.getStartPay());
		}

		if (StringUtils.isNotBlank(dto.getEndPay())) {
			sql.append(" 			AND reg.fee_total + reg.givemoney_total <= ").append(dto.getEndPay());
		}

		sql.append(" 	    )\n  ");
		sql.append(" 	group by\n ");
		sql.append(" 		d_kid,\n ");
		sql.append(" 		game_main, \n");
		sql.append(" 		imei, \n");
		sql.append(" 		idfa, \n ");
		sql.append(" 		oaid, \n ");
		sql.append(" 		mac, \n");
		sql.append(" 		moneyAmount,\n");
		sql.append(" 		loginDays ,\n");
		sql.append(" 		diff \n ");
		sql.append(" 	)\n ");
		sql.append(" group by\n ");
		sql.append(" 	d_kid,\n");
		sql.append(" 	game_main,\n ");
		sql.append(" 	imei,\n");
		sql.append(" 	idfa,\n ");
		sql.append(" 	oaid,\n ");
		sql.append(" 	mac, \n");
		sql.append(" 	payfee,  \n");
		sql.append(" 	activenum \n");
		sql.append(" HAVING \n");
		sql.append(" 	1 = 1 \n");
		if (StringUtils.isNotBlank(dto.getActiveStartNum())) {
			sql.append(" 	and activenum >= ").append(dto.getActiveStartNum());
		}
		if (StringUtils.isNotBlank(dto.getActiveEndNum())) {
			sql.append(" 	and activenum <= ").append(dto.getActiveEndNum());
		}
		if(StringUtils.isNotBlank(dto.getActiveStartMaxNum())) {
			sql.append(" 	and activemaxnum >= ").append(dto.getActiveStartMaxNum());
		}
		if(StringUtils.isNotBlank(dto.getActiveEndMaxNum())) {
			sql.append(" 	and activemaxnum <= ").append(dto.getActiveEndMaxNum());
		}
		sql.append(" order by payfee desc,activenum desc \n");
		if (isPage) {
			sql.append("LIMIT  ").append(dto.offset()).append(", ").append(dto.getSize()).append('\n');
		}
		return sql;
	}

	private RowMapper<AudiencePackVo> audienceMapping(AudiencePackDto dto) {
		return (rs, idx) -> {
			final AudiencePackVo queryBean = new AudiencePackVo();
			queryBean.setPgid(requireInteger(rs.getObject("pgid")));
			queryBean.setImei(rs.getString("imei"));
			queryBean.setIdfa(rs.getString("idfa"));
			queryBean.setOaid(rs.getString("oaid"));
			queryBean.setMac(rs.getString("mac"));
			queryBean.setPayfee(requireBigDecimal(rs.getBigDecimal("payfee"), 2));
			queryBean.setActivenum(requireInteger(rs.getObject("activenum")));
			queryBean.setActivemaxnum(requireInteger(rs.getObject("activemaxnum")));
			return queryBean;
		};
	}

	private static Integer requireInteger(Object value) {
		if (null == value) {
			return null;
		}
		if (value instanceof Number) {
			return ((Number) value).intValue();
		}
		throw new NumberFormatException(value.getClass() + " 不可转换为 java.lang.Integer");
	}

	private static Long requireLong(Object value) {
		if (null == value) {
			return null;
		}
		if (value instanceof Number) {
			return ((Number) value).longValue();
		}
		throw new NumberFormatException(value.getClass() + " 不可转换为 java.lang.Long");
	}

	private static BigDecimal requireBigDecimal(BigDecimal value, int scala) {
		if (null == value) {
			return null;
		}
		return value.setScale(scala, RoundingMode.HALF_UP);
	}

}
